const express = require('express');
const router = express.Router();
const db = require('../db');
const FreightCalculator = require('../utils/freightCalculator');

/**
 * 获取所有省份列表
 */
router.get('/', (req, res) => {
  try {
    const provinces = db.all(`
      SELECT id, name, calc_type, created_at 
      FROM provinces 
      ORDER BY name ASC
    `);
    res.json({
      success: true,
      data: provinces
    });
  } catch (err) {
    res.status(500).json({
      success: false,
      error: err.message
    });
  }
});

/**
 * 获取省份及其费率段详情
 */
router.get('/:id', (req, res) => {
  try {
    const provinceId = parseInt(req.params.id);
    if (isNaN(provinceId)) {
      return res.status(400).json({
        success: false,
        error: '无效的省份ID'
      });
    }
    
    // 获取省份信息
    const province = db.get(`
      SELECT id, name, calc_type 
      FROM provinces 
      WHERE id = ?
    `, [provinceId]);
    
    if (!province) {
      return res.status(404).json({
        success: false,
        error: '省份不存在'
      });
    }
    
    // 获取费率段
    const rates = db.all(`
      SELECT id, weight_min, weight_max, rate, base_fee, sort_num 
      FROM freight_rates 
      WHERE province_id = ? 
      ORDER BY sort_num ASC
    `, [provinceId]);
    
    res.json({
      success: true,
      data: {
        ...province,
        rates: rates
      }
    });
  } catch (err) {
    res.status(500).json({
      success: false,
      error: err.message
    });
  }
});

/**
 * 添加省份及费率段
 */
router.post('/with-rates', (req, res) => {
  try {
    const { name, calc_type, rates } = req.body;
    
    // 基础验证
    if (!name || name.trim() === '') {
      return res.status(400).json({
        success: false,
        error: '省份名称不能为空'
      });
    }
    
    const validCalcTypes = ['weight_step', 'fixed_add', 'piece'];
    if (!validCalcTypes.includes(calc_type)) {
      return res.status(400).json({
        success: false,
        error: '无效的计费方式'
      });
    }
    
    if (!Array.isArray(rates) || rates.length === 0) {
      return res.status(400).json({
        success: false,
        error: '至少需要配置一个费率段'
      });
    }
    
    // 验证费率段数据
    for (let i = 0; i < rates.length; i++) {
      const rate = rates[i];
      const index = i + 1; // 显示给用户的序号（从1开始）
      
      // 验证最小重量
      if (rate.weightMin === undefined || rate.weightMin === null || isNaN(rate.weightMin) || rate.weightMin < 0) {
        return res.status(400).json({
          success: false,
          error: `第${index}个费率段：最小重量不能为空且必须是非负数`
        });
      }
      
      // 验证费率
      if (rate.rate === undefined || rate.rate === null || isNaN(rate.rate) || rate.rate < 0) {
        return res.status(400).json({
          success: false,
          error: `第${index}个费率段：费率不能为空且必须是非负数`
        });
      }
      
      // 验证排序号
      if (rate.sortNum === undefined || rate.sortNum === null || isNaN(rate.sortNum) || rate.sortNum < 0) {
        return res.status(400).json({
          success: false,
          error: `第${index}个费率段：排序号不能为空且必须是非负数`
        });
      }
      
      // 验证最大重量（如果提供）
      if (rate.weightMax !== null && rate.weightMax !== undefined) {
        if (isNaN(rate.weightMax) || rate.weightMax <= rate.weightMin) {
          return res.status(400).json({
            success: false,
            error: `第${index}个费率段：最大重量必须大于最小重量`
          });
        }
      }
    }
    
    // 检查省份名称是否已存在
    const existingProvince = db.get(`
      SELECT id FROM provinces WHERE name = ?
    `, [name.trim()]);
    
    if (existingProvince) {
      return res.status(400).json({
        success: false,
        error: `省份"${name}"已存在，请使用不同的名称`
      });
    }
    
    // 开始事务：先添加省份，再添加费率段
    db.run('BEGIN TRANSACTION');
    
    try {
      // 添加省份
      const provinceResult = db.run(`
        INSERT INTO provinces (name, calc_type) 
        VALUES (?, ?)
      `, [name.trim(), calc_type]);
      
      const provinceId = provinceResult.lastInsertRowid;
      
      // 添加费率段
      const rateParams = rates.map(rate => [
        provinceId,
        parseFloat(rate.weightMin),
        rate.weightMax !== null && rate.weightMax !== undefined ? parseFloat(rate.weightMax) : null,
        parseFloat(rate.rate),
        rate.baseFee !== undefined && rate.baseFee !== null ? parseFloat(rate.baseFee) : 0,
        parseInt(rate.sortNum)
      ]);
      
      db.batchInsert(`
        INSERT INTO freight_rates 
          (province_id, weight_min, weight_max, rate, base_fee, sort_num)
        VALUES (?, ?, ?, ?, ?, ?)
      `, rateParams);
      
      // 提交事务
      db.run('COMMIT');
      
      res.status(201).json({
        success: true,
        message: `省份"${name}"及费率段添加成功`,
        data: {
          provinceId: provinceId
        }
      });
    } catch (err) {
      // 回滚事务
      db.run('ROLLBACK');
      throw err;
    }
  } catch (err) {
    res.status(500).json({
      success: false,
      error: err.message
    });
  }
});

/**
 * 计算运费
 */
router.post('/calculate', (req, res) => {
  try {
    const { provinceId, weight, pieceCount = 1 } = req.body;
    
    // 验证参数
    if (isNaN(provinceId) || provinceId <= 0) {
      return res.status(400).json({
        success: false,
        error: '无效的省份ID'
      });
    }
    
    if (isNaN(weight) || weight < 0) {
      return res.status(400).json({
        success: false,
        error: '重量必须是非负数'
      });
    }
    
    if (isNaN(pieceCount) || pieceCount < 1) {
      return res.status(400).json({
        success: false,
        error: '件数必须是大于0的整数'
      });
    }
    
    // 获取省份和费率段
    const province = db.get(`
      SELECT id, name, calc_type 
      FROM provinces 
      WHERE id = ?
    `, [provinceId]);
    
    if (!province) {
      return res.status(404).json({
        success: false,
        error: '省份不存在'
      });
    }
    
    const rates = db.all(`
      SELECT weight_min, weight_max, rate, base_fee, sort_num 
      FROM freight_rates 
      WHERE province_id = ?
    `, [provinceId]);
    
    if (rates.length === 0) {
      return res.status(400).json({
        success: false,
        error: `省份"${province.name}"未配置费率段，无法计算运费`
      });
    }
    
    // 计算运费
    const calculator = new FreightCalculator(province, rates);
    const freight = calculator.calculate(parseFloat(weight), parseInt(pieceCount));
    
    res.json({
      success: true,
      data: {
        province: province.name,
        weight: parseFloat(weight),
        pieceCount: parseInt(pieceCount),
        freight: freight,
        calcType: province.calc_type
      }
    });
  } catch (err) {
    res.status(400).json({
      success: false,
      error: err.message
    });
  }
});

/**
 * 删除省份（会级联删除费率段）
 */
router.delete('/:id', (req, res) => {
  try {
    const provinceId = parseInt(req.params.id);
    if (isNaN(provinceId)) {
      return res.status(400).json({
        success: false,
        error: '无效的省份ID'
      });
    }
    
    // 检查省份是否存在
    const province = db.get(`
      SELECT id FROM provinces WHERE id = ?
    `, [provinceId]);
    
    if (!province) {
      return res.status(404).json({
        success: false,
        error: '省份不存在'
      });
    }
    
    // 删除省份（会触发外键级联删除费率段）
    db.run(`
      DELETE FROM provinces WHERE id = ?
    `, [provinceId]);
    
    res.json({
      success: true,
      message: '省份及相关费率段已删除'
    });
  } catch (err) {
    res.status(500).json({
      success: false,
      error: err.message
    });
  }
});

module.exports = router;
